﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    /// <summary>
    /// 权限数据访问类访问类
    /// </summary>
    public class SysRightService
    {
        #region 角色管理
        /// <summary>
        /// 获取所有的角色
        /// </summary>
        public List<AdminRoles> GetAllRoles()
        {
            string sql = "select RoleId,RoleName,RoleDesc from AdminRoles where RoleId not in (10,11)";
            List<AdminRoles> adminRoles = new List<AdminRoles>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql);

            while (reader.Read())
            {
                adminRoles.Add(new AdminRoles
                {
                    RoleId = Convert.ToInt32(reader["RoleId"]),
                    RoleName = reader["RoleName"].ToString(),
                    RoleDesc = reader["RoleDesc"].ToString(),
                });
            }
            reader.Close();
            return adminRoles;
        }

        /// <summary>
        /// 新增角色
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="roleDesc"></param>
        /// <returns></returns>
        public int AddRole(string roleName, string roleDesc)
        {
            string sql = "insert into AdminRoles(RoleName,RoleDesc) values(@RoleName,@RoleDesc)";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleName",roleName),
                new SqlParameter("@RoleDesc",roleDesc)
            };

            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改角色
        /// </summary>
        /// <param name="roleId"></param>
        /// <param name="roleName"></param>
        /// <param name="roleDesc"></param>
        /// <returns></returns>
        public int ModifyRole(int roleId, string roleName, string roleDesc)
        {
            string sql = "update AdminRoles set RoleName=@RoleName,RoleDesc=@RoleDesc where RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleName",roleName),
                new SqlParameter("@RoleDesc",roleDesc),
                new SqlParameter("@RoleId",roleId)
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="RoleDesc"></param>
        /// <returns></returns>
        public int DeleteRole(int roleId)
        {
            string sql = "delete from AdminRoles where RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleId",roleId)
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                {
                    throw new Exception("该角色被其他用户所使用，请接触关联后在删除");
                }
                else
                    throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 管理员
        /// <summary>
        /// 获取所有管理员用户
        /// </summary>
        /// <returns></returns>
        public List<SysAdmin> GetAllAdmin()
        {
            string sql = "select AdminId,AdminName,SysAdmins.RoleId,RoleName,StatusId from SysAdmins inner join AdminRoles on SysAdmins.RoleId =AdminRoles.RoleId order by AdminId";
            List<SysAdmin> sysAdminList = new List<SysAdmin>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql);
            while (reader.Read())
            {
                sysAdminList.Add(new SysAdmin
                {
                    AdminId = Convert.ToInt32(reader["AdminId"]),
                    AdminName = reader["AdminName"].ToString(),
                    RoleId= Convert.ToInt32(reader["RoleId"]),
                    RoleName = reader["RoleName"].ToString(),
                    StatusId = Convert.ToInt32(reader["StatusId"]),
                }); 
            }
            reader.Close();
            return sysAdminList;
        }

        /// <summary>
        /// 添加管理员
        /// </summary>
        /// <param name="sysAdmin"></param>
        /// <returns></returns>
        public int AddAdmin(SysAdmin sysAdmin)
        {
            string sql = "insert into SysAdmins(AdminName, LoginPwd, RoleId) values(@AdminName, @LoginPwd, @RoleId);select @@identity";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("AdminName",sysAdmin.AdminName),
                new SqlParameter("LoginPwd",sysAdmin.LoginPwd),
                new SqlParameter("RoleId",sysAdmin.RoleId),
            };
            try
            {
                var adminId= Convert.ToInt32(SQLHelperForRightDB.GetSingleResult(sql, sqlParameters));
                string sql1 = $"update SysAdmins set LoginId={adminId} where AdminId={adminId}" ;
                return SQLHelperForRightDB.Update(sql1);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 修改管理员姓名
        /// </summary>
        /// <param name="adminId"></param>
        /// <param name="adminName"></param>
        /// <returns></returns>
        public int ModifyAdminName(string adminId, string adminName)
        {
            string sql = $"update SysAdmins set AdminName=@AdminName where AdminId=@AdminId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("AdminName",adminName),
                new SqlParameter("AdminId",adminId),
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改管理员姓名
        /// </summary>
        /// <param name="adminId"></param>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public int ModifyAdminRole(string adminId, string roleId)
        {
            string sql = $"update SysAdmins set RoleId=@RoleId where AdminId=@AdminId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("RoleId",roleId),
                new SqlParameter("AdminId",adminId),
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除管理员
        /// </summary>
        /// <param name="adminId"></param>
        /// <returns></returns>
        public int DeleteAdmin(string adminId)
        {
            string sql = $"delete from SysAdmins where AdminId=@AdminId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("AdminId",adminId),
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 开启或者禁用管理员
        /// </summary>
        /// <param name="statusId"></param>
        /// <returns></returns>
        public int ModifyAdminStatus(string adminId,int statusId)
        {
            string sql = $"update SysAdmins set StatusId=@StatusId where AdminId=@AdminId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("StatusId",statusId),
                new SqlParameter("AdminId",adminId),
            };
            try
            {
                return SQLHelperForRightDB.Update(sql, sqlParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 权限管理:获取权限目录树
        /// <summary>
        /// 获取以及权限
        /// </summary>
        /// <returns></returns>
        public List<FirstMenu> GetFirstMenus()
        {
            string sql = " select FirstMenuId,DisplayName from FirstMenu";
            List<FirstMenu> list = new List<FirstMenu>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql);
            while (reader.Read())
            {
                list.Add(new FirstMenu
                {
                    FirstMenuId = Convert.ToInt32(reader["FirstMenuId"]),
                    DisplayName = reader["DisplayName"].ToString()
                }); ;
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// 根据一级菜单Id获取对应二级菜单
        /// </summary>
        /// <returns></returns>
        public List<SecondMenu> GetSecondMenu(int firstMenuId)
        {
            string sql = "select SecondMenuId,DisplayName,FirstMenuId from SecondMenu where FirstMenuId=@FirstMenuId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@FirstMenuId",firstMenuId)
            };
            List<SecondMenu> list = new List<SecondMenu>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql, sqlParameters);
            while (reader.Read())
            {
                list.Add(new SecondMenu
                {
                    FirstMenuId = Convert.ToInt32(reader["FirstMenuId"]),
                    DisplayName = reader["DisplayName"].ToString(),
                    SecondMenuId = Convert.ToInt32(reader["SecondMenuId"]),
                }); ;
            }
            reader.Close();
            return list;
        }


        /// <summary>
        /// 根据二级菜单Id获取对应权限点
        /// </summary>
        /// <param name="secondMenuId"></param>
        /// <returns></returns>
        public List<RightList> GetRightCode(int secondMenuId)
        {
            string sql = "select RightId,RightName,RightCode,SecondMenuId from RightList where SecondMenuId=@SecondMenuId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@SecondMenuId",secondMenuId)
            };
            List<RightList> list = new List<RightList>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql, sqlParameters);
            while (reader.Read())
            {
                list.Add(new RightList
                {
                    RightId = Convert.ToInt32(reader["RightId"]),
                    RightName = reader["RightName"].ToString(),
                    RightCode = reader["RightCode"].ToString(),
                    SecondMenuId = Convert.ToInt32(reader["SecondMenuId"]),
                }); ;
            }
            reader.Close();
            return list;
        }
        #endregion

        #region 查询角色权限
        /// <summary>
        /// 获取一级菜单
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<FirstMenu> GetRoleFirstMenus(string roleId)
        {
            string sql = " select  distinct FirstMenuId,FirstMenu from View_RightQuery where RoleId=@RoleId";
            List<FirstMenu> list = new List<FirstMenu>();
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleId",roleId)
            };
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql, sqlParameters);
            while (reader.Read())
            {
                list.Add(new FirstMenu
                {
                    FirstMenuId = Convert.ToInt32(reader["FirstMenuId"]),
                    DisplayName = reader["FirstMenu"].ToString()
                }); ;
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// 根据一级菜单Id获取对应二级菜单
        /// </summary>
        /// <param name="firstMenuId"></param>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<SecondMenu> GetRoleSecondMenu(int firstMenuId, string roleId)
        {
            string sql = "select  distinct SecondMenuId,SecondMenu from View_RightQuery where FirstMenuId=@FirstMenuId and RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@FirstMenuId",firstMenuId),  
                new SqlParameter("@RoleId",roleId)
            };
            List<SecondMenu> list = new List<SecondMenu>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql, sqlParameters);
            while (reader.Read())
            {
                list.Add(new SecondMenu
                {
                    DisplayName = reader["SecondMenu"].ToString(),
                    SecondMenuId = Convert.ToInt32(reader["SecondMenuId"]),
                }); ;
            }
            reader.Close();
            return list;
        }


        /// <summary>
        /// 根据二级菜单Id获取对应权限点
        /// </summary>
        /// <param name="secondMenuId"></param>
        /// <returns></returns>
        public List<RightList> GetRoleRightCode(int secondMenuId, string roleId)
        {
            string sql = "select RightId,RightName,RightCode,SecondMenuId from View_RightQuery where SecondMenuId=@SecondMenuId and RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@SecondMenuId",secondMenuId),
                new SqlParameter("@RoleId",roleId)
            };
            List<RightList> list = new List<RightList>();
            SqlDataReader reader = SQLHelperForRightDB.GetReader(sql, sqlParameters);
            while (reader.Read())
            {
                list.Add(new RightList
                { 
                    RightId = Convert.ToInt32(reader["RightId"]),
                    RightName = reader["RightName"].ToString(),
                    RightCode = reader["RightCode"].ToString(),
                    SecondMenuId = Convert.ToInt32(reader["SecondMenuId"]),
                }); 
            }
            reader.Close();
            return list;
        }
        #endregion

        #region 角色权限设定
        /// <summary>
        /// 查询该角色是否已经具备该权限
        /// </summary>
        /// <param name="roleId"></param>
        /// <param name="rightId"></param>
        /// <returns></returns>
        public bool QueryRoleRightIsExists(string roleId, string rightId)
        {
            string sql = "select Count(1) from RoleRights where RightId=@RightId and RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleId",roleId),
                new SqlParameter("@RightId",rightId)
            };
            var count = Convert.ToInt32(SQLHelperForRightDB.GetSingleResult(sql, sqlParameters));
            if (count > 0) return true;
            else return false;

        }

        /// <summary>
        /// 给角色添加权限
        /// </summary>
        /// <param name="roleId"></param>
        /// <param name="rightId"></param>
        /// <returns></returns>
        public int AddRoleRight(string roleId, string rightId)
        {
            string sql = "insert into RoleRights(RightId,RoleId) values(@RightId,@RoleId)";
            SqlParameter[] sqlParameters = new SqlParameter[]
           {
                new SqlParameter("@RoleId",roleId),
                new SqlParameter("@RightId",rightId)
           };
            try
            {
                return Convert.ToInt32(SQLHelperForRightDB.Update(sql, sqlParameters));
            }
            catch (Exception ex)
            {
   
             throw ex;
            }
        }

        /// <summary>
        /// 给角色删除权限
        /// </summary>
        /// <param name="roleId"></param>
        /// <param name="rightId"></param>
        /// <returns></returns>
        public int DeleteRoleRight(string roleId, string rightId)
        {
            string sql = "delete from RoleRights where  RightId=@RightId and RoleId=@RoleId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@RoleId",roleId),
                new SqlParameter("@RightId",rightId)
            };
            try
            {
                return Convert.ToInt32(SQLHelperForRightDB.Update(sql, sqlParameters));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


    }
}
